﻿using System.Data.SqlClient;
using System;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.ComponentModel;
using System.Data;
using System.IO;
using System.Text;
using System.Windows.Forms;
using ExcelToTPI.Utility;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NLog;
using NPOI.XSSF.UserModel;

namespace ExcelToTPI
{

    public partial class Form1 : Form
    {
        private IWorkbook workbook = null;       // 工作薄对象

        private ISheet sheet = null;                // 数据表单

        private FileStream fsExcel = null;          // 工作薄数据流

        private DataTable dtExcelData = null;       // Excel数据表中的数据

        private string connectionStringFormat = "Server={0},{1};Database={2};User Id={3};Password={4};";       // SQL Server连接串
        private string connectionString = string.Empty;
        private string SQLServerTableName = string.Empty;               // 当前选定的SQL Server数据表

        private Dictionary<string, string> columnMapList = null; // 字段映射列表 (key: Excel列头 value:TPI列名)

        public Form1()
        {
            this.InitializeComponent();
            backgroundWorker1.WorkerReportsProgress = true;
            backgroundWorker1.WorkerSupportsCancellation = true;
        }

        private void Form1_Load(object sender, EventArgs e)
        {
        }

        /// <summary>
        /// 检测数据库连接
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        #region 检测数据库连接
        private void Button_ConnectServer_Click(object sender, EventArgs e)
        {
            try
            {
                string serverIP = txtSqlServerIP.Text.Trim();
                if (string.IsNullOrEmpty(txtSqlServerPort.Text))
                {
                    MessageBox.Show("请填写SQL Server 端口号，默认为1433。");
                    return;
                }
                int iPort;
                if (!int.TryParse(txtSqlServerPort.Text, out iPort))
                {
                    MessageBox.Show("端口号格式不正确！");
                    return;
                }
                if (!CheckSqlServerConnect(serverIP, iPort, txtSqlServerUserName.Text,
                    txtSqlServerPassword.Text))
                {
                    MessageBox.Show("SQL Server 数据库连接失败！");

                }
                else
                {
                    MessageBox.Show("SQL Server 数据库连接成功！");
                    this.BindSQLDBList();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        #endregion
        private bool CheckSqlServerConnect(string serverIP, int port, string userName, string password)
        {
            bool result = false;
            connectionString = string.Format("Server ={0},{1};Database=master;User Id = {2}; Password ={3}", serverIP, port, userName, password);
            SqlConnection connection = new SqlConnection(connectionString);
            try
            {
                connection.Open();
                result = true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                connection.Close();
                connection.Dispose();
            }
            return result;
        }

        private bool CheckSqlServerConnect(string serverIP, int port, string databaseName, string userName, string password)
        {
            bool result = false;
            connectionString = string.Format(connectionStringFormat, serverIP, port, databaseName, userName, password);
            SqlConnection connection = new SqlConnection(connectionString);
            try
            {
                connection.Open();
                result = true;
            }
            catch (Exception ex)
            {

            }
            finally
            {
                connection.Close();
                connection.Dispose();
            }
            return result;
        }
        /// <summary>
        /// 获取数据库列表
        /// </summary>
        private void BindSQLDBList()
        {
            string strDBList = "Select   name   from   master..sysdatabases where name   not   in('master','model','master','tempdb','northwind','pubs')";
            DataSet ds = SqlHelper.ExecuteDataset(connectionString, CommandType.Text, strDBList);
            if (ds != null && ds.Tables.Count > 0)
            {
                DataTable dt = ds.Tables[0];
                if (dt == null || dt.Rows.Count < 1)
                {
                    MessageBox.Show("获取数据库列表失败，请检查连接是否正确。", "获取数据库", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return;
                }
                this.cbxSqlServerDatabase.ValueMember = "Name";
                this.cbxSqlServerDatabase.DisplayMember = "Name";
                this.cbxSqlServerDatabase.DataSource = dt;
            }
        }
        /// <summary>
        /// 获取 SQL Server 数据表字段
        /// </summary>
        private void BindSqlServerTableList()
        {
            string databaseName = cbxSqlServerDatabase.Text.Trim();
            string strSql = string.Format("SELECT Name FROM {0}..SysObjects Where XType='U' ORDER BY Name", databaseName);
            string sqlAdminConnectionString= string.Format($"Server ={txtSqlServerIP.Text},{txtSqlServerPort.Text};Database=master;User Id = {txtSqlServerUserName.Text}; Password ={txtSqlServerPassword.Text}");
            DataSet ds = SqlHelper.ExecuteDataset(sqlAdminConnectionString, CommandType.Text, strSql);
            if (ds != null && ds.Tables.Count > 0)
            {
                DataTable dt = ds.Tables[0];
                this.combSqlServerTable.ValueMember = "Name";
                this.combSqlServerTable.DisplayMember = "Name";
                this.combSqlServerTable.DataSource = dt;
            }
        }

        /// <summary>
        /// 计算文件MD5值
        /// </summary>
        /// <param name="path">文件路径</param>
        /// <returns></returns>
        #region 计算文件MD5值
        public static string FileToMD5(string path)
        {
            try
            {
                using (System.IO.FileStream fs = new System.IO.FileStream(path, System.IO.FileMode.Open))
                {
                    System.Security.Cryptography.MD5CryptoServiceProvider md5 = new System.Security.Cryptography.MD5CryptoServiceProvider();
                    byte[] b = md5.ComputeHash(fs);
                    return System.BitConverter.ToString(b).Replace("-", "");
                }
            }
            catch (Exception e)
            {
                return e.ToString();
            }
        }
        #endregion

        /// <summary>
        /// 数据库列表选中发生改变的处理
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        #region 数据库列表选中发生改变的处理
        private void combSqlServerTable_SelectedIndexChanged(object sender, EventArgs e)
        {
            string tableName = combSqlServerTable.SelectedValue as string;
            if (!string.IsNullOrEmpty(tableName))
            {
                this.BindTableColumnsList();
            }
        }
        #endregion

        /// <summary>
        /// 选择源文件所在的目录
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        #region 选择源文件所在的目录
        private void Button_SelectSourceFolder_Click(object sender, EventArgs e)
        {
            try
            {
                DialogResult result = this.openFileDialog_SelectExcelFile.ShowDialog();
                if (result == DialogResult.OK)
                {
                    string strFileName = this.openFileDialog_SelectExcelFile.FileName;

                    if (strFileName != "")
                    {
                        string strExt = strFileName.Substring(strFileName.LastIndexOf(".") + 1);
                        if (strExt.ToLower() == "xls" || strExt.ToLower() == "xlsx")
                        {
                            this.TextBox_SourceExcelFile.Text = strFileName;
                            fsExcel = new FileStream(strFileName, FileMode.Open, FileAccess.Read);
                            if (strExt.ToLower() == "xls")
                            {
                                workbook = new HSSFWorkbook(fsExcel);
                            }
                            else
                            {
                                workbook = new XSSFWorkbook(fsExcel);
                            }
                            this.comboBox_Sheet.Items.Clear();
                            for (int i = 0; i < workbook.NumberOfSheets; i++)
                            {
                                this.comboBox_Sheet.Items.Add(workbook.GetSheetName(i));
                            }
                        }
                        else
                        {
                            MessageBox.Show("请选择Excel文件!");
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(string.Format("异常信息：{0} \r\n 堆栈信息：{1}", ex.Message, ex.StackTrace));
            }
        }
        #endregion

        #region 工作薄中，选定的数据表发生改变时，重新绑定字段映射关系

        private void comboBox_Sheet_SelectedIndexChanged(object sender, EventArgs e)
        {
            BindTableColumnsList();
        }

        /// <summary>
        /// 绑定数据列映射关系
        /// </summary>
        private void BindTableColumnsList()
        {
            int iRowCount = 0;  // 所需配置的最大行数
            StringCollection scSqlServerTableColumns = new StringCollection();
            StringCollection scExcelColumns = new StringCollection();
            StringCollection scCommonColumns = new StringCollection();
            // 获取SQL Server表字段
            string sqlServerTableName = combSqlServerTable.SelectedValue as string;

            string strSql = string.Format("select name from syscolumns where id=object_id('{0}')", sqlServerTableName);
            string sqlAdminConnectionString = string.Format($"Server ={txtSqlServerIP.Text},{txtSqlServerPort.Text};Database=master;User Id = {txtSqlServerUserName.Text}; Password ={txtSqlServerPassword.Text}");

            IDataReader columnReader = SqlHelper.ExecuteReader(sqlAdminConnectionString, CommandType.Text, strSql);
            if (columnReader != null)
            {
                while (columnReader.Read())
                {
                    scSqlServerTableColumns.Add((columnReader["name"] as string ?? "").Trim().ToUpper());
                }
                columnReader.Close();
            }

            // 获取Excel表头
            string sheetName = comboBox_Sheet.SelectedItem as string;
            if (!string.IsNullOrEmpty(sheetName))
            {
                sheet = workbook.GetSheet(sheetName);
                // Excel表单含有表头时，重新绑定列映射关系
                if (sheet.GetRow(0).PhysicalNumberOfCells > 0)
                {
                    foreach (ICell cell in sheet.GetRow(0).Cells)
                    {
                        scExcelColumns.Add(cell.StringCellValue.Trim().ToUpper());
                    }
                    iRowCount = scSqlServerTableColumns.Count > scExcelColumns.Count
                        ? scExcelColumns.Count
                        : scSqlServerTableColumns.Count;
                    if (iRowCount > 0)
                    {
                        dgvColumnMap.Rows.Clear();
                        dgvColumnMap.Rows.Add(iRowCount);

                        // 获取公共字段
                        foreach (string item in scExcelColumns)
                        {
                            if (scSqlServerTableColumns.Contains(item))
                            {
                                scCommonColumns.Add(item);
                            }
                        }

                        for (int i = 0; i < iRowCount; i++)
                        {
                            DataGridViewComboBoxCell cbTPIColumns = new DataGridViewComboBoxCell();
                            foreach (string item in scSqlServerTableColumns)
                            {
                                cbTPIColumns.Items.Add(item);
                            }
                            DataGridViewComboBoxCell cbExcelColumns = new DataGridViewComboBoxCell();
                            foreach (string item in scExcelColumns)
                            {
                                cbExcelColumns.Items.Add(item);
                            }
                            cbTPIColumns.Items.Insert(0, "");
                            cbExcelColumns.Items.Insert(0, "");

                            // 匹配公共字段
                            if (i < scCommonColumns.Count)
                            {
                                cbTPIColumns.Value = scCommonColumns[i];
                                cbExcelColumns.Value = scCommonColumns[i];
                            }
                            dgvColumnMap.Rows[i].Cells["dgvcbcTPIField"] = cbTPIColumns;
                            dgvColumnMap.Rows[i].Cells["dgvcbcExcelHeader"] = cbExcelColumns;
                        }
                    }
                }
            }
            else
            {
                MessageBox.Show(sheetName + "中无数据信息！");
            }
        }

        #endregion

        /// <summary>
        /// 单元格选定时，弹出字段列表
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void dgvColumnMap_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            if (e.ColumnIndex >= 0 && e.RowIndex >= 0 && dgvColumnMap[e.ColumnIndex, e.RowIndex] != null && !dgvColumnMap[e.ColumnIndex, e.RowIndex].ReadOnly)
            {
                DataGridViewComboBoxColumn comboBoxColumn = dgvColumnMap.Columns[e.ColumnIndex] as DataGridViewComboBoxColumn;
                if (comboBoxColumn != null)
                {
                    dgvColumnMap.BeginEdit(true);
                    DataGridViewComboBoxEditingControl comboBoxEditingControl = dgvColumnMap.EditingControl as DataGridViewComboBoxEditingControl;
                    if (comboBoxEditingControl != null)
                    {
                        comboBoxEditingControl.DroppedDown = true;
                    }
                }
            }
        }

        /// <summary>
        /// 开始对应的处理
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void Button_StartRelate_Click(object sender, EventArgs e)
        {
            try
            {
                // 获取用户配置的字段映射关系
                columnMapList = new Dictionary<string, string>();
                foreach (DataGridViewRow dgvr in dgvColumnMap.Rows)
                {
                    DataGridViewComboBoxCell dgvcbcExcelColumn = dgvr.Cells[0] as DataGridViewComboBoxCell;
                    DataGridViewComboBoxCell dgvcbcTpiColumn = dgvr.Cells[1] as DataGridViewComboBoxCell;
                    if (dgvcbcExcelColumn != null && dgvcbcTpiColumn != null && dgvcbcExcelColumn.Value != null
                        && dgvcbcTpiColumn.Value != null)
                    {
                        if (!string.IsNullOrEmpty(dgvcbcExcelColumn.Value.ToString())
                            && !string.IsNullOrEmpty(dgvcbcTpiColumn.Value.ToString()))
                        {
                            columnMapList.Add(dgvcbcExcelColumn.Value.ToString(), dgvcbcTpiColumn.Value.ToString());
                        }
                    }
                }

                dtExcelData = new DataTable();
                // 填充DataTable列头
                for (int i = sheet.GetRow(0).FirstCellNum; i < sheet.GetRow(0).LastCellNum; i++)
                {
                    LogManager.GetCurrentClassLogger().Debug(string.Format("LastCellNum:{0} i:{1}", sheet.GetRow(0).LastCellNum, i));
                    dtExcelData.Columns.Add(sheet.GetRow(0).Cells[i].StringCellValue);
                }
                for (int i = sheet.FirstRowNum + 1; i < sheet.LastRowNum + 1; i++)
                {
                    DataRow dr = dtExcelData.NewRow();
                    IRow row = sheet.GetRow(i);
                    if (row == null)
                    {
                        continue;
                    }
                    for (int j = row.FirstCellNum; j < sheet.GetRow(0).LastCellNum; j++)
                    {
                        ICell cell = row.GetCell(j);
                        if (cell != null)
                        {
                            switch (cell.CellType)
                            {
                                case CellType.Numeric:
                                    dr[j] = cell.NumericCellValue.ToString();
                                    break;
                                case CellType.Boolean:
                                    dr[j] = cell.BooleanCellValue.ToString();
                                    break;
                                case CellType.String:
                                    dr[j] = cell.StringCellValue;
                                    break;
                            }
                        }
                    }
                    dtExcelData.Rows.Add(dr);
                }
                SQLServerTableName = combSqlServerTable.SelectedValue as string; // 获取选定的SqlServer表名称

                if (backgroundWorker1.IsBusy != true)
                {
                    // Start the asynchronous operation.
                    backgroundWorker1.RunWorkerAsync();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("导入Excel发生异常 异常信息：" + ex.Message + " 堆栈信息：" + ex.StackTrace);
            }
        }

        private void btnCancel_Click(object sender, EventArgs e)
        {
            if (backgroundWorker1.WorkerSupportsCancellation)
            {
                // Cancel the asynchronous operation.
                backgroundWorker1.CancelAsync();
            }
        }

        private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
        {
            BackgroundWorker worker = sender as BackgroundWorker;
            try
            {
                if (sheet != null)
                {
                    StringBuilder columnsBuilder = new StringBuilder();
                    StringBuilder columnsPlaceholderBuilder = new StringBuilder();
                    foreach (string valueItem in columnMapList.Values)
                    {
                        columnsBuilder.Append(valueItem + ",");
                        columnsPlaceholderBuilder.Append("@" + valueItem + ",");
                    }
                    string strSql = string.Format("insert into {0}({1}) values({2})", SQLServerTableName, columnsBuilder.ToString().TrimEnd(','), columnsPlaceholderBuilder.ToString().TrimEnd(','));
                    List<SqlParameter> parameters = new List<SqlParameter>();
                    foreach (DataRow row in dtExcelData.Rows)
                    {
                        foreach (KeyValuePair<string, string> kvp in columnMapList)
                        {
                            SqlParameter parameter = new SqlParameter(kvp.Value, SqlDbType.VarChar) { Value = row[kvp.Key] };
                            parameters.Add(parameter);
                        }
                        int iResult = SqlHelper.ExecuteNonQuery(connectionString, CommandType.Text, strSql, parameters.ToArray());
                        parameters.Clear();
                    }
                }
                else
                {
                    MessageBox.Show("请选择要导入的Excel表单！");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("导入Excel发生异常 异常信息：" + ex.Message + " 堆栈信息：" + ex.StackTrace);
            }
        }

        private void backgroundWorker1_ProgressChanged(object sender, ProgressChangedEventArgs e)
        {
            this.ProgressBar1.Value = e.ProgressPercentage;
        }

        private void backgroundWorker1_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            MessageBox.Show("数据导入完成！");
        }

        private void btnTpiToExcel_Click(object sender, EventArgs e)
        {
            TpiToExcel tte = new TpiToExcel();
            tte.Owner = this;
            this.Hide();
            tte.Show();
        }

        private void cbxSqlServerDatabase_SelectedIndexChanged(object sender, EventArgs e)
        {
            connectionString = string.Format($"Server ={txtSqlServerIP.Text},{txtSqlServerPort};Database={cbxSqlServerDatabase.SelectedItem};User Id = {txtSqlServerUserName.Text}; Password ={txtSqlServerPassword.Text}");
            this.BindSqlServerTableList();
        }
    }
}